/**
 * SQLite3数据库初始化模块
 * 1. npm install sqlite3
 * 2. API文档:https://github.com/TryGhost/node-sqlite3/wiki/API
 */

//导入Node.js文件模块
const fs = require("fs");
//导入SQLite3模块
const sqlite3 = require("sqlite3").verbose();
//导入Node.js系统模块
const os = require('os');
//导入工具模块
const util = require("../../common/util");
//导入配置模块
const config = require("../../common/config");

// 获取本地主机的IP地址
let systemIp = '';
const interfaces = os.networkInterfaces();
for (const devName in interfaces) {
  const iface = interfaces[devName];
  for (const alias of iface) {
    if (alias.family === 'IPv4' && !alias.internal) {
      systemIp = alias.address;
      break;
    }
  }
  if (systemIp) {
    break;
  }
}

//SQL语句：创建User数据表
const sqlCreateUser = `
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  username VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  nickname VARCHAR(255),
  truename VARCHAR(255),
  avatar VARCHAR(255),
  role INTEGER NOT NULL,
  current_point INTEGER DEFAULT 0,
  phone_number VARCHAR(255),
  email VARCHAR(255), 
  des VARCHAR(255),      
  last_login_time DATETIME NOT NULL,
  last_login_ip VARCHAR(255) NOT NULL,
  login_count INTEGER DEFAULT 0,
  created_time DATETIME NOT NULL,
  created_ip VARCHAR(255) NOT NULL,
  updated_time DATETIME NOT NULL,
  UNIQUE (phone_number), 
  UNIQUE (email)         
);
`;

//SQL语句：创建User数据表中的第一个用户
const sqlCreateAdminUser = `
INSERT INTO users(
  username, password, nickname, truename, avatar,
  role, current_point, phone_number, email, des,
  last_login_time, last_login_ip, login_count, created_time, created_ip, updated_time
  ) VALUES (
  'admin', '${util.md5pwd("123456")}', '系统管理员', '超级管理员', './images/default.png', 
  1, 0, '13798742062', '2756689256@qq.com', '我是超级管理员',
  CURRENT_TIMESTAMP, '${systemIp}', 0, ${Date.now()}, '${systemIp}', ${Date.now()}
  );
`;

//SQL语句：创建Teacher数据表
const sqlCreateTeacher = `CREATE TABLE teachers (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  username VARCHAR(255) NOT NULL UNIQUE,
  avatar VARCHAR(255),
  role INTEGER NOT NULL,
  des VARCHAR(255),
  subject VARCHAR(255),
  create_time DATETIME NOT NULL,
  phone_number VARCHAR(255),
  email VARCHAR(255), 
  UNIQUE (phone_number), 
  UNIQUE (email)         
);`
  ;

//SQL语句：创建Teacher数据表中的高数教师
const sqlCreateMathTeacher = `INSERT INTO teachers (username, avatar, role, des, subject, create_time, phone_number, email)
VALUES ('宋浩', './images/songhao.jpg', 3, '擅长高等数学教学', '理学', ${Date.now()}, '13800138000', 'songhao@example.com');
`;


//SQL语句：创建Teacher数据表中的英语教师
const sqlCreateEnglishTeacher = `INSERT INTO teachers (username, avatar, role, des, subject, create_time, phone_number, email)
VALUES ('田静', './images/tianjing.jpg', 3, '专业英语教师，多年教学经验', '外语学', ${Date.now()}, '13800138001', 'tianjing@example.com');
`;

//SQL语句：创建Course数据表
const sqlCreateCourse = `
CREATE TABLE courses (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  teacher_id INTEGER NOT NULL,
  name VARCHAR(50) NOT NULL,
  subject VARCHAR(255),
  category VARCHAR(50),
  price DECIMAL(10,2) NOT NULL,
  difficulty VARCHAR(50),
  role VARCHAR(10) NOT NULL CHECK (role IN ('付费课', '免费课')),
  des VARCHAR(255),
  avatar VARCHAR(255),
  created_time DATETIME NOT NULL,
  updated_time DATETIME NOT NULL,
  status INTEGER,
  average_rating DECIMAL(5,1),
  rating_count INTEGER
);
`;

//SQL语句：创建Order数据表中的课程
const sqlCreateOrder = `CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    user_id INT NOT NULL,
    course_id INT NOT NULL,
    order_total DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    course_source VARCHAR(50) NOT NULL,
    order_time DATETIME NOT NULL,
    transaction_id VARCHAR(100) DEFAULT NULL
);`

//创建评论帖子数据表
const sqlCreateComment = `CREATE TABLE comments (  
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  user_id INT NOT NULL,  
  course_id INT NOT NULL,   
  parent_comment_id INT DEFAULT 0, 
  content TEXT NOT NULL, 
  created_at DATETIME NOT NULL,  
  updated_at DATETIME NOT NULL,
  useful INTEGER DEFAULT 0,
  useless INTEGER DEFAULT 0,
)
`;


/**
 * SqliteDB构造函数
 * @constructor
 */
function SqliteDB() {
  this.dbFile = config.dbFile;
  //如果数据库nylibrary.db文件不存在则创建数据库文件
  if (!fs.existsSync(this.dbFile)) {
    util.log("创建SQLite数据库文件【OnlineCourse.db】");
    this.initDB();
  }
}

/**
 * 执行初始化数据库的SQL语句
 */
SqliteDB.prototype.initDB = function () {
  let db = new sqlite3.Database(this.dbFile, (err) => {
    if (err) {
      util.err(err);
    }
  });
  db.serialize(() => {
    db.run(sqlCreateUser);
    util.log("数据表User创建成功");
    db.run(sqlCreateAdminUser);
    util.log("管理员用户创建成功");
    db.run(sqlCreateTeacher);
    util.log("教师数据表创建成功");
    db.run(sqlCreateMathTeacher);
    util.log("教师宋浩创建成功");
    db.run(sqlCreateEnglishTeacher);
    util.log("教师田静创建成功");
    db.run(sqlCreateCourse);
    util.log("课程表Course创建成功");
    db.run(sqlCreateOrder);
    util.log("订单表Order创建成功");
    db.run(sqlCreateComment);
    util.log("数据表Comments创建成功");
  });
  //SQLite数据库是基于文件，大量的资源消耗在文件的打开与关闭上会降低效率
  //   db.close((err) => {
  //     if (err) {
  //       util.err(err);
  //     }
  //   });
};

module.exports = SqliteDB